<?php
// set include path
set_include_path(get_include_path() . PATH_SEPARATOR . "/home/matt/work/wiisocial/build/wiisocial/src");
require_once "data/dao/BaseDAO.php";

/**
 * This class loads the ip to country data from
 * http://ip-to-country.webhosting.info/node/view/6
 * into the database table ipToCountry.
 * 
 * It can be invoked with the phing target
 * db-update-ip-to-country-table
 * 
 */
class IPToCountryTableCreator extends BaseDAO{
	
	function IPToCountryTableCreator(){

		if(!file_exists(self::IP_TO_COUNTRY_FILE)){
			throw new Exception("File not found: " . self::IP_TO_COUNTRY_FILE);
		}

		$this->createAndUpdateTable();
	}
	
	const IP_TO_COUNTRY_FILE = "ip-to-country.csv";
	
	private function createAndUpdateTable(){
		
		// drop table
		$this->update("DROP TABLE ipToCountry");
		
		// create table
		$this->update("CREATE TABLE ipToCountry(
			IP_FROM double,
			IP_TO double,
			COUNTRY_CODE2 char(2),
			COUNTRY_CODE3 char(3),
			COUNTRY_NAME varchar(50))"
		);
		
		// insert data
		$contents = file(self::IP_TO_COUNTRY_FILE);
		
		// [76072] => "3577607728","3577607735","GB","GBR","UNITED KINGDOM"
		foreach ($contents as $line){
			
			$lineArray = explode(",", str_replace('"', '', $line));
//			echo "1: " . $lineArray[0];
//			echo "2: " . $lineArray[1];
//			echo "3: " . $lineArray[2];
//			echo "4: " . $lineArray[3];
//			echo "5: " . $lineArray[4];
			
			$this->update("insert into ipToCountry(IP_FROM, IP_TO, COUNTRY_CODE2, 
				COUNTRY_CODE3, COUNTRY_NAME) values('%s', '%s', '%s', '%s', '%s')", 
				array($lineArray[0], $lineArray[1], $lineArray[2], $lineArray[3],
					$lineArray[4]));
		}
	}
}
$iptctc = new IPToCountryTableCreator();
?>